package com.zxy.shaozhiyi1.main;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.openqa.selenium.By;
import org.openqa.selenium.By.ById;
import org.openqa.selenium.JavascriptExecutor;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.openqa.selenium.support.ui.ExpectedCondition;
import org.openqa.selenium.support.ui.Select;
import org.openqa.selenium.support.ui.WebDriverWait;
import org.testng.annotations.Test;

import com.zxy.shaozhiyi1.util.date.DateUtils;
import com.zxy.shaozhiyi1.util.excel.ExcelUtil;
import com.zxy.shaozhiyi1.util.excel.ExcelWrite;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class ShaoZhiYi2
{
	public WebDriver driver = null;
	public WebElement webelement = null;
	// excel工作簿
	private static HSSFWorkbook workbook = null;
	
	@Test(invocationCount = 1)
	public void exportData() throws Exception
	{
		// Google浏览器
		// 经实际对比效果，Google浏览器较为稳定
		System.setProperty("webdriver.chrome.driver", "chromedriver.exe");
		WebDriver driver = new ChromeDriver();
		driver.get(Constant.URL);
		System.out.println("Page Title is " + driver.getTitle());
		// AssertJUnit.assertEquals("Google", driver.getTitle());
		
		// 休息25s，等待用户操作（输入检索条件：学科，年份，输入4位图片验证码，点击检索按钮）
		// 经实际运行测试，发现25s的等待时间是一个比较科学的等待时间，对用户来说既充足，也不会太浪费时间
		String jiansuoyemian_datagridyemian_str = Constant.JIANSUOYEMIAN_DATAGRIDYEMIAN;
		int jiansuoyemian_datagridyemian_int = Integer.parseInt(jiansuoyemian_datagridyemian_str);
		// 默认20s
		Thread.sleep(jiansuoyemian_datagridyemian_int);
		
		// excel文件和sheet名字取当前时间
		String shortYMDHMS = DateUtils.getShortYMDHMS();
		// 最后输出的文件
		String filePath = "E:" + File.separator + "data_" + shortYMDHMS + ".xls";
		filePath = "excel_out" + File.separator + "data_" + shortYMDHMS + ".xls";
		// 创建新excel
        createExcel(filePath, shortYMDHMS, null);

		int pageNo = 1;
		// 起始行0
		int rowIndex = 0;
		// 导出页数
		String exprotPageCountStr = Constant.TEST;
		int exprotPageCountInt = Integer.parseInt(exprotPageCountStr);
		while (pageNo < exprotPageCountInt + 1)
		{
			if(pageNo != 1)
			{
				// 如果不是第一次进入dataGrid结果页，让程序等待15s给用户输入
				// 休息20s等待用户操作（输入4位图片验证码，点击下一页按钮）
				// 经实际运行测试，发现在dataGrid结果页，20s的等待时间是一个比较科学的等待时间，对用户来说既充足，也不会太浪费时间
				String datagridyemian_datagridyemian_str = Constant.DATAGRIDYEMIAN_DATAGRIDYEMIAN;
				int datagridyemian_datagridyemian_int = Integer.parseInt(datagridyemian_datagridyemian_str);
				// 默认20s
				Thread.sleep(datagridyemian_datagridyemian_int);
			}
			
			// 从结果页面取到dataGrid
			webelement = driver.findElement(By.xpath("//table[@id = \"dataGrid\"]"));
			// 取到dataGrid中所有的tr元素（即：取到所有的行，包括了表头行）
			List<WebElement> webelementtrs = webelement.findElements(By.tagName("tr"));
			
			// 不管页面是否加载成功，表头行肯定存在，所以先移除表头行
			webelementtrs.remove(0);
			int size = webelementtrs.size();
			System.out.println("当前页中总共有      " +size + "   行数据");

			// excel写入接口需要一个json数组
			JSONArray jsonArray = new JSONArray();
			// 所有行
			int rowNo = 1;
			for (WebElement webElementtr : webelementtrs)
			{
				JSONObject jsonObject = new JSONObject();
				System.out.println("第" + rowNo + "行的数据为：" + webElementtr.getText());
				
				// 取到所有td元素（即：取到所有的列）
				List<WebElement> webElementtds = webElementtr.findElements(By.tagName("td"));
				int columenNo = 1;
				// 所有列
				for (WebElement webElementtd : webElementtds)
				{
					String columnText = webElementtd.getText();
					System.out.println("第" + rowNo + "行      第" + columenNo + "列的数据为：" + columnText);
					jsonObject.put("columen" + columenNo, columnText);
					columenNo++;
				}
				
				jsonArray.add(jsonObject);
				rowNo++;
			}
			System.out.println("要往excel中存入的封装好的数据为：   " + jsonArray.toString());
			
			// 用LinkedHashMap，避免列头无序的问题。
			Map<String, String> headMap = new LinkedHashMap<>();
			headMap.put("columen1", "value_1");
			headMap.put("columen2", "value_2");
			headMap.put("columen3", "value_3");
			headMap.put("columen4", "value_4");
			headMap.put("columen5", "value_5");
			headMap.put("columen6", "value_6");
			headMap.put("columen7", "value_7");
			headMap.put("columen8", "value_8");
			
			// 以时间命名
//			String shortYMDHMS = DateUtils.getShortYMDHMS();
			
	        // excel导出
			excel(filePath, headMap, jsonArray, rowIndex, 20, null);
			System.out.println("第      " + pageNo + "   页的数据保存完毕");
			
			// 起始行计算
			rowIndex = pageNo * 12 + 1;
			// 页数计算
			pageNo++;
		}

		Thread.sleep(10000);
		
		// driver.quit();
	}
	
	/**
     * 创建新excel
     * 
     * @param filePath excel的路径
     * @param sheetName 要创建的表格索引
     * @param titleRow excel的第一行即表格头
	 * @throws FileNotFoundException
     */
    public static void createExcel(String filePath, String sheetName, String titleRow[]) throws FileNotFoundException
    {  
        //新建文件
        FileOutputStream fileOutputStream = null;
        fileOutputStream = new FileOutputStream(filePath);
        try
        {
            //添加表头
            /*HSSFRow row = workbook.getSheet(sheetName).createRow(0);    //创建第一行
            for(short i = 0;i < titleRow.length;i++)
            {
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(titleRow[i]);
            }  */
            
        	//创建workbook
            workbook = new HSSFWorkbook();
            //添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
            HSSFSheet sheet = workbook.createSheet(sheetName);
            //创建第一行
//          HSSFRow row = workbook.getSheet(sheetName).createRow(0);
            //创建第一行
            HSSFRow firstRow = sheet.createRow(0);
            workbook.write(fileOutputStream);
        }
        catch (Exception e)
        {
        	e.printStackTrace();
        }
        finally
        {
            try
            {
                fileOutputStream.close();
            }
            catch (IOException e)
            {
                e.printStackTrace();
            }
        }    
    }
    
	/**
	 * excel导出
	 * 
	 * @param filePath 输出的文件
	 * @param headMap 表头
	 * @param jsonArray 表体
	 * @param rowIndex 起始行
	 * @param colWidth 列宽
	 * @param datePattern 日期格式
	 * @param pageNo 页号
	 */
	public void excel(String filePath, Map<String, String> headMap, 
			          JSONArray jsonArray, int rowIndex, 
			          int colWidth, String datePattern)
	{
		// 输出流
		try {
			FileInputStream fileInputStream = new FileInputStream(filePath);
//			Workbook workbook = getWorkbook(fileInputStream);
			
			/*Row row = sheet1.createRow(sheet1.getLastRowNum() + 1);
			row.setHeightInPoints((short) 25);
			// 给这一行赋值
			row.createCell(0).setCellValue("12");
			row.createCell(1).setCellValue("23");
			FileOutputStream os = new FileOutputStream(filePath);
			workbook.write(os);
			fileInputStream.close();
			os.close();

			FileInputStream is2 = new FileInputStream(filePath);
			Workbook wb2 = getWorkbook(is2);
			System.out.println("aaa");
			Sheet sheet12 = wb2.getSheetAt(0);
			System.out.println(sheet12.getLastRowNum());
			Row row2 = sheet12.createRow(sheet12.getLastRowNum() + 1);
			row2.setHeightInPoints((short) 25);
			// 给这一行赋值
			row2.createCell(0).setCellValue("12");
			row2.createCell(1).setCellValue("23");
			FileOutputStream os2 = new FileOutputStream(filePath);
			wb2.write(os2);
			is2.close();
			os2.close();*/
			
			
			Sheet sheet = workbook.getSheetAt(0);
			System.out.println("sheet名字为-----------------------------------------" + sheet.getSheetName());
			System.out.println("最后一行的行号是：  " + sheet.getLastRowNum());
			
			// 列头样式
			HSSFCellStyle headerStyle = (HSSFCellStyle) workbook.createCellStyle();
			headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			HSSFFont headerFont = (HSSFFont) workbook.createFont();
			headerFont.setFontHeightInPoints((short) 12);
			headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			headerStyle.setFont(headerFont);
			
			// 设置列宽
			int minBytes = colWidth < Constant.DEFAULT_COLOUMN_WIDTH ? Constant.DEFAULT_COLOUMN_WIDTH : colWidth;// 至少字节数
			int[] arrColWidth = new int[headMap.size()];
			
			// 产生表格标题行,以及设置列宽
			String[] properties = new String[headMap.size()];
			String[] headers = new String[headMap.size()];
			int ii = 0;
			for (Iterator<String> iter = headMap.keySet().iterator(); iter.hasNext();) {
				String fieldName = iter.next();

				properties[ii] = fieldName;
				headers[ii] = fieldName;

				// 设置列宽
				int bytes = fieldName.getBytes().length;
				arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
				sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
				ii++;
			}
			
			boolean bl = false;
			int qishihang = -1;
			// 遍历集合数据，产生数据行
			for (Object obj : jsonArray)
			{
				// 产生表头
				if (rowIndex == 65535 || rowIndex == 0)
				{
					if (rowIndex != 0)
					sheet = workbook.createSheet();// 如果数据超过了，则在第二页显示
					
//					sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));
					// 列头
//					HSSFRow headerRow = (HSSFRow) sheet.createRow(rowIndex);
					/*HSSFRow headerRow = (HSSFRow) sheet1.createRow(sheet1.getLastRowNum() + 1);
					for (int i = 0; i < headers.length; i++)
					{
						headerRow.createCell(i).setCellValue(headers[i]);
						headerRow.getCell(i).setCellStyle(headerStyle);

					}*/
					// rowIndex = 2;//数据内容从 rowIndex=2开始
//					rowIndex++;
				}
				
				if (datePattern == null)
					datePattern = Constant.DEFAULT_DATE_PATTERN;
				
				// JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
				JSONObject jsonObject = JSONObject.fromObject(obj);
//				HSSFRow dataRow = (HSSFRow) sheet.createRow(rowIndex);
				int lashRowNum = sheet.getLastRowNum();
				System.out.println("最后一行的行号是：----------：" + lashRowNum);
				HSSFRow dataRow;
				if(lashRowNum == 0 && qishihang == -1)
				{
					dataRow = (HSSFRow) sheet.createRow(lashRowNum);
				}
				else
				{
					dataRow = (HSSFRow) sheet.createRow(lashRowNum + 1);
				}
				qishihang++;
				
				
//				HSSFRow dataRow = (HSSFRow) sheet1.createRow(sheet1.getLastRowNum() + 1);
				
				for (int i = 0; i < properties.length; i++)
				{
					HSSFCell newCell = dataRow.createCell(i);

					Object o = jsonObject.get(properties[i]);
					String cellValue = "";
					if (o == null)
						cellValue = "";
					else if (o instanceof Date)
						cellValue = new SimpleDateFormat(datePattern).format(o);
					else
						cellValue = o.toString();

					newCell.setCellValue(cellValue);
					
//					newCell.setCellStyle(cellStyle);
				}
				rowIndex++;
			}
			
			FileOutputStream fileOutputStream = new FileOutputStream(filePath);
			workbook.write(fileOutputStream);
			fileInputStream.close();
			fileOutputStream.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static Workbook getWorkbook(InputStream is) throws IOException {
		Workbook wb = null;
		if (!is.markSupported())
		{
			is = new PushbackInputStream(is, 8);
		}
		if (POIFSFileSystem.hasPOIFSHeader(is))
		{
			// Excel2003及以下版本
			wb = (Workbook) new HSSFWorkbook(is);
		}
		else if (POIXMLDocument.hasOOXMLHeader(is))
		{
			// Excel2007及以上版本
			wb = new XSSFWorkbook(is);
		}
		else
		{
			throw new IllegalArgumentException("你的Excel版本目前poi无法解析！");
		}
		// }
		return wb;
	}
	
	public static void main(String[] args) throws Exception
    {
    	//创建文件
//      String title[] = {"id","name","password"};
//      createExcel("E:/test222.xls","sheet1",title);
    	
    	// excel文件和sheet名字取当前时间
    	String shortYMDHMS = DateUtils.getShortYMDHMS();
		// 最后输出的文件
		String filePath = "E:" + File.separator + "data_" + shortYMDHMS + ".xls";
		// 创建新excel
        createExcel(filePath, null, null);
	}
}